package db.jdbc;

import db.ClubApplicationRepository;
import domain.Club;
import domain.ClubAdministrator;
import domain.ClubApplication;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import web.PaginationSupport;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

/**
 * 社团申请资源库接口的jdbc实现类
 *
 * @author liujiale
 */
@Repository
public class JdbcClubApplicationRepository implements ClubApplicationRepository {
    private static final String INSERT_ClubApplication = "insert into clubApplication(applicationTime,status,description) values(?,?,?)";
    private static final String INSERT_club_clubApplication = "insert into club_clubApplication(clubID,clubApplicationID) values(?,?)";
    private static final String INSERT_clubAdministrators_clubApplication = "insert into clubAdministrators_clubApplication(studentID,clubApplicationID) values(?,?)";

    private static final String DELETE_ClubApplication = "delete from clubApplication where clubApplicationID=?";
    private static final String DELETE_club_clubApplication = "delete from club_clubApplication where clubApplicationID=?";
    private static final String DELETE_clubAdministrators_clubApplication = "delete from clubAdministrators_clubApplication where clubApplicationID=?";

    private static final String UPDATE_ClubApplication = "update clubApplication set status=?,description=? where clubApplicationID=?";
    private static final String SELECT_ClubApplication = "select * from clubApplication";
    private static final String SELECT_ClubApplication_Pagination = SELECT_ClubApplication + " order by clubApplicationID  limit ? offset  ?";

    private static JdbcTemplate jdbc;

    @Autowired
    public JdbcClubApplicationRepository(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    /**
     * 获取申请的总数
     *
     * @return 申请的数量
     */
    @Override
    public long count() {
        return jdbc.queryForInt("select count(clubApplicationID) from clubApplication");
    }

    /**
     * 创建一个新的申请
     *
     * @param clubApplication 新申请的信息
     * @return 新创建的申请
     */
    @Override
    public ClubApplication save(ClubApplication clubApplication) {
        jdbc.update(INSERT_ClubApplication, clubApplication.getApplicationTime(), clubApplication.getStatus(), clubApplication.getDescription());
        jdbc.update(INSERT_clubAdministrators_clubApplication, clubApplication.getApplicant().getStudentID(), clubApplication.getClubApplicationID());
        jdbc.update(INSERT_club_clubApplication, clubApplication.getPendingClub().getClubID(), clubApplication.getClubApplicationID());
        return clubApplication;
    }

    /**
     * 根据ID查找申请
     *
     * @param clubApplicationID 申请的ID
     * @return 对应ID的申请
     */
    @Override
    public ClubApplication findOne(int clubApplicationID) {
        return jdbc.queryForObject(SELECT_ClubApplication + " where clubApplicationID=?", new ClubApplicationRowMapper(), clubApplicationID);
    }

    /**
     * 更新申请
     *
     * @param clubApplication 更新后的申请信息
     * @return 是否更新成功
     */
    @Override
    public boolean updateClubApplication(ClubApplication clubApplication) {
        return jdbc.update(UPDATE_ClubApplication, clubApplication.getStatus(), clubApplication.getDescription(), clubApplication.getClubApplicationID()) != 0;
    }

    /**
     * 分页查找申请
     *
     * @param pageNo   页码
     * @param pageSize 每页的数量
     * @return 分页的申请列表
     */
    @Override
    public PaginationSupport<ClubApplication> findPage(int pageNo, int pageSize) {
        int totalCount = (int) count();
        int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
        if (totalCount < 1) {
            return new PaginationSupport<ClubApplication>(new ArrayList<ClubApplication>(0), 0);
        }

        List<ClubApplication> items = jdbc.query(SELECT_ClubApplication_Pagination, new ClubApplicationRowMapper(), pageSize, startIndex);
        return new PaginationSupport<ClubApplication>(items, totalCount, pageSize, startIndex);
    }

    /**
     * 删除申请
     *
     * @param clubApplicationID 申请的ID
     * @return 是否删除成功
     */
    @Override
    public boolean deleteClubApplication(int clubApplicationID) {
        ClubApplication clubApplication=findOne(clubApplicationID);
        if(clubApplication==null){
            return false;
        }
        jdbc.update(DELETE_club_clubApplication,clubApplicationID);
        jdbc.update(DELETE_clubAdministrators_clubApplication,clubApplicationID);
        return jdbc.update(DELETE_ClubApplication,clubApplicationID)!=0;
    }

    /**
     * 获取所有申请
     *
     * @return 所有申请的列表
     */
    @Override
    public List<ClubApplication> findAll() {
        return jdbc.query(SELECT_ClubApplication+" order by clubApplicationID", new ClubApplicationRowMapper());
    }

    static class ClubApplicationRowMapper implements RowMapper<ClubApplication> {
        /**
         * 将数据库查询结果映射到ClubApplication对象
         *
         * @param rs 结果集
         * @param i 行号
         * @return ClubApplication对象
         * @throws SQLException SQL异常
         */
        public ClubApplication mapRow(ResultSet rs, int i) throws SQLException {
            // 从结果集获取clubApplication字段，并构造ClubApplication对象
            int clubApplicationID = rs.getInt("clubApplicationID");
            ClubApplication ca = new ClubApplication(rs.getTimestamp("applicationTime"),
                    rs.getInt("status"),
                    rs.getString("description"),
                    clubApplicationID);

            // 查询申请人信息并设置到ClubApplication
            ClubAdministrator caAdministrator = jdbc.queryForObject("select ca.* from clubAdministrator as ca, clubadministrators_clubapplication as cc where ca.clubAdminID = cc.clubAdminID and cc.clubApplicationID=?",
                    new JdbcClubAdministratorRepository.ClubAdministratorRowMapper(),clubApplicationID);
            ca.setApplicant(caAdministrator);

            // 查询申请的社团信息并设置到ClubApplication
            Club club = jdbc.queryForObject("select club.* from club,club_clubapplication as cc where cc.clubID=club.clubID and cc.clubApplicationID=?",
                    new JdbcClubRepository.ClubRowMapper(),clubApplicationID);
            Vector<ClubAdministrator> caList = new Vector<>();
            caList.add(caAdministrator);
            club.setAdminList(caList);
            ca.setPendingClub(club);

            // 返回构造的ClubApplication对象
            return ca;
        }
    }
}
